package com.eascs.workflow.sys.dao.impl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StringUtil;
import com.eascs.workflow.common.dao.BaseDao;
import com.eascs.workflow.common.entity.sys.ActOrgDetail;
import com.eascs.workflow.common.entity.sys.ActOrgEntity;
import com.eascs.workflow.common.entity.sys.ActOrgUser;
import com.eascs.workflow.sys.dao.SysOrgDao;

@Repository
public class SysOrgDaoImpl extends BaseDao implements SysOrgDao {
	private static Logger log = LoggerFactory.getLogger(SysOrgDaoImpl.class);

	@Override
	public List<ActOrgEntity> getOrgListByLevel(String parentId, int level) {
		List<ActOrgEntity> list = new ArrayList<>();
		if (StringUtil.hasText(parentId)) {
			list = getAllOrgListByParentIdAndLevel(parentId, null, level);
			List<ActOrgEntity> list1 = new ArrayList<>();
			// 如果查找到的组织下面还有同一级的子组织，则合并
			for (ActOrgEntity org : list) {
				list1.add(org);
				String subsql = "SELECT * FROM act_org WHERE parent_id = ? and level = ?";
				list1.addAll(
						super.findEntitys(subsql.toString(), ActOrgEntity.class, org.getOrgId(), org.getOrgLevel()));
			}
			list = list1;
		} else {
			String sql = "SELECT * FROM act_org WHERE level = ?";
			list = super.findEntitys(sql.toString(), ActOrgEntity.class, level);
			if (level == ActOrgEntity.ORG_LEVEL_PROVINCE && null != list) {
				String sql1 = "select level from act_org where id = ?";
				List<ActOrgEntity> dataList = new ArrayList<>();
				for (ActOrgEntity orgEntity : list) {
					int parentLevel = super.findInteger(sql1, orgEntity.getParentId());
					if (parentLevel != ActOrgEntity.ORG_LEVEL_PROVINCE) {
						dataList.add(orgEntity);
					}
				}
				list = dataList;
			}
		}
		return list;
	}

	/**
	 * 递归查询指定parentId下面所有指定level的组织
	 * 
	 * @param parentId
	 * @param preList
	 *            用于递归时记录上一levl的同级组织
	 * @param level
	 * @return
	 */
	@Override
	public List<ActOrgEntity> getAllOrgListByParentIdAndLevel(String parentId, List<ActOrgEntity> preList, int level) {
		if (null == preList) {
			preList = new ArrayList<>();
		}
		List<ActOrgEntity> list = new ArrayList<>();
		String sql = "SELECT * FROM act_org WHERE parent_id = ?";
		list = super.findEntitys(sql.toString(), ActOrgEntity.class, parentId);
		if (list.isEmpty())
			return list;
		int currLevel = list.get(0).getOrgLevel();
		String currOrgId = list.get(0).getOrgId();
		if (level == currLevel) {
			// 查找并合并与上级组织同级的组织机构
			if (!preList.isEmpty()) {
				list = new ArrayList<>();
				for (ActOrgEntity org : preList) {
					String sql1 = "SELECT * FROM act_org WHERE parent_id = ? and level = ? ";
					list.addAll(super.findEntitys(sql1.toString(), ActOrgEntity.class, org.getOrgId(), level));
				}
			}
			return list;
		}
		preList = list;
		return getAllOrgListByParentIdAndLevel(currOrgId, preList, level);
	}

	@Override
	public List<Object[]> getOrgListLikeName(String orgName, int level) {
		String sql = "SELECT * from (SELECT unit_no,CONCAT_WS('-',unit_code,org_name) as org_name FROM act_org WHERE /**DATA_PERMISSION(unit_no)**/ level = ?) t "
				+ " where t.org_name LIKE ? LIMIT 10";
		return super.findEntitys(sql, Object[].class, level, "%" + orgName + "%");
	}

	@Override
	public List<Object[]> getOrgAllLikeName(String orgName) {
		String sql = "SELECT id,org_name FROM act_org WHERE  org_name LIKE ? LIMIT 10";
		return super.findEntitys(sql, Object[].class, "%" + orgName + "%");
	}

	@Override
	public List<ActOrgEntity> getOrgAllList() {
		String sql = "SELECT id,org_name,parent_id,unit_no,unit_code,level,org_full_name,office_address,sponsor_tel,register_address,is_natural_province,province,city FROM act_org";
		return super.findEntitys(sql, ActOrgEntity.class, new Object[] {});
	}

	@Override
	public ActOrgEntity getOrgByIds(String ids) {
		String sql = "SELECT * FROM act_org WHERE id = ?";
		return super.findEntity(sql, ActOrgEntity.class, new Object[] { ids });
	}

	@Override
	public int insert(ActOrgEntity orgEntity) {
		String unitCode = orgEntity.getUnitCode();
		if (StringUtils.isNoneBlank(unitCode) && !"null".equals(unitCode)) {
			String unitNo = getUnitsNoByUnitsCode(unitCode);
			if (StringUtils.isNoneBlank(unitNo) && !"null".equals(unitNo)) {
				orgEntity.setUnitNo(unitNo);
			}
		}
		return super.workflowJdbcSession.insert(orgEntity);
	}

	@Override
	public int update(ActOrgEntity orgEntity) {
		String updateSql = "UPDATE act_org SET org_name=?,parent_id=?,unit_no=?,unit_code=?,LEVEL=?,org_full_name=?,office_address=?,sponsor_tel=?,register_address=?,is_natural_province=?,province=?,city=? WHERE id=?";
		String unitCode = orgEntity.getUnitCode();
		if (StringUtils.isNoneBlank(unitCode) && !"null".equals(unitCode)) {
			String unitNo = getUnitsNoByUnitsCode(unitCode);
			if (StringUtils.isNoneBlank(unitNo) && !"null".equals(unitNo)) {
				orgEntity.setUnitNo(unitNo);
			}
		}
		return super.workflowJdbcSession.execute(updateSql,
				new Object[] { orgEntity.getOrgName(), orgEntity.getParentId(), orgEntity.getUnitNo(),
						orgEntity.getUnitCode(), orgEntity.getOrgLevel(), orgEntity.getOrgFullName(),
						orgEntity.getOfficeAddress(), orgEntity.getSponsorTel(), orgEntity.getRegisterAddress(),
						orgEntity.getIsNaturalProvince(), orgEntity.getProvince(), orgEntity.getCity(),
						orgEntity.getOrgId() });
	}

	@Override
	public int delete(String ids) {
		ActOrgEntity entity = new ActOrgEntity();
		entity.setOrgId(ids);
		return super.workflowJdbcSession.deleteByIdWithEntity(entity);
	}

	@Override
	public String getUnitsNoByUnitsCode(String unitsCode) {
		String sql = "SELECT UNITNO FROM hr_units WHERE UNITCODE=?";
		return super.workflowJdbcSession.findInteger(sql, unitsCode) + "";
	}

	@Override
	public ActOrgDetail getOrgDetailByUnitCode(String unitCode) {
		ActOrgDetail orgDetail = null;
		try {
			log.info("unitCode:[{}],开始获取组织结构信息", unitCode);
			String sql = "select u.PROVINCE as provinceCode,u.UNITCODE as unitCode,u.UNITNO as unitNo,u.NAMECNSHORT as orgName from hr_units u where u.UNITCODE = ?";
			orgDetail = workflowJdbcSession.findEntity(sql, ActOrgDetail.class, unitCode);
			if (orgDetail == null) {
				log.info("unitCode:[{}],开始获取组织结构失败", unitCode);
				return null;
			}
			if (StringUtils.isNotBlank(orgDetail.getProvinceCode())) {
				sql = "select p.letter_code from pub_provinces p where p.`code` = ?";
				String letterCode = workflowJdbcSession.findString(sql, orgDetail.getProvinceCode());
				if (StringUtils.isBlank(letterCode)) {
					orgDetail.setProvinceLetterCode("HO");
				} else {
					orgDetail.setProvinceLetterCode(letterCode);
				}
			} else {
				orgDetail.setProvinceLetterCode("HO");
			}
			return orgDetail;
		} catch (Exception e) {
			log.error("unitCode:[" + unitCode + "],开始获取组织结构失败", e);
			return null;
		}
	}

	@Override
	public ActOrgDetail getOrgDetailByUnitNo(String unitNo) {
		ActOrgDetail orgDetail = null;
		try {
			log.info("unitNo:[{}],开始获取组织结构信息", unitNo);
			String sql = "select u.PROVINCE as provinceCode,u.UNITCODE as unitCode,u.UNITNO as unitNo,u.NAMECNSHORT as orgName from hr_units u where u.UNITNO = ?";
			orgDetail = workflowJdbcSession.findEntity(sql, ActOrgDetail.class, unitNo);
			if (orgDetail == null) {
				log.info("unitNo:[{}],开始获取组织结构失败", unitNo);
				return null;
			}
			if (StringUtils.isNotBlank(orgDetail.getProvinceCode())) {
				sql = "select p.letter_code from pub_provinces p where p.`code` = ?";
				String letterCode = workflowJdbcSession.findString(sql, orgDetail.getProvinceCode());
				if (StringUtils.isBlank(letterCode)) {
					orgDetail.setProvinceLetterCode("HO");
				} else {
					orgDetail.setProvinceLetterCode(letterCode);
				}
			} else {
				orgDetail.setProvinceLetterCode("HO");
			}
			return orgDetail;
		} catch (Exception e) {
			log.error("unitNo:[" + unitNo + "],开始获取组织结构失败", e);
			return null;
		}
	}

	@Override
	public ActOrgEntity getOrgUnitNo(String unitNo) {
		String sql = "SELECT * FROM act_org WHERE unit_no = ?";
		return super.findEntity(sql, ActOrgEntity.class, new Object[] { unitNo });
	}

	@Override
	public List<ActOrgEntity> getOrgByParentIds(List<String> parentIds) {
		String sql = "SELECT * FROM act_org WHERE parent_id in ";
		StringBuffer parentIdBuf = new StringBuffer("(");
		for (String parentId : parentIds) {
			parentIdBuf.append("'").append(parentId).append("'").append(",");
		}
		String params = null;
		if (parentIdBuf.length() > 1) {
			params = parentIdBuf.substring(0, parentIdBuf.length() - 1) + ")";
			sql += params;
			return super.findEntitys(sql, ActOrgEntity.class);
		}
		return new ArrayList<ActOrgEntity>();
	}

	@Override
	public PageResult<ActOrgUser> queryPageActOrgUser(List<String> orgIds, int pageSize, int pageIndex) {
		String sql = "SELECT * FROM act_org_user WHERE is_remove =0 and org_id IN ";
		StringBuffer parentIdBuf = new StringBuffer("(");
		for (String orgId : orgIds) {
			parentIdBuf.append("'").append(orgId).append("'").append(",");
		}
		String params = null;
		if (parentIdBuf.length() > 1) {
			params = parentIdBuf.substring(0, parentIdBuf.length() - 1) + ")";
			sql += params;
		}
		SqlPageQuery<ActOrgUser> pageQuery = new SqlPageQuery<ActOrgUser>(ActOrgUser.class, pageSize, pageIndex, sql);
		return this.findDataWithPage(pageQuery);
	}

	@Override
	public int insertActOrgUser(ActOrgUser actOrgUser) {
		return super.workflowJdbcSession.insert(actOrgUser);
	}

	@Override
	public int deleteActOrgUser(String id) {
		ActOrgUser orgUser = new ActOrgUser();
		orgUser.setId(id);
		return super.workflowJdbcSession.deleteByIdWithEntity(orgUser);
	}

	public ActOrgUser getOrgUserByUserNo(String userNo) {
		String sql = "SELECT * FROM act_org_user WHERE is_remove =0 and user_no = ?";
		return super.findEntity(sql, ActOrgUser.class, userNo);
	}

	@Override
	public ActOrgEntity getActOrgByUserNo(String userNo) {
		String sql = "SELECT t2.* FROM act_org_user t1, act_org t2 WHERE t1.org_id = t2.id and t1.is_remove =0 AND t1.user_no=?";
		return super.findEntity(sql, ActOrgEntity.class, userNo);
	}

	@Override
	public List<ActOrgEntity> getAllOrgParentList(String orgId, List<ActOrgEntity> parentList) {
		if (null == parentList) {
			parentList = new ArrayList<>();
		}
		ActOrgEntity org = super.getById(ActOrgEntity.class, orgId);
		if (null != org && "-1".equals(org.getParentId())) {
			parentList.add(org);
			return parentList;
		}
		parentList.add(org);
		return getAllOrgParentList(org.getParentId(), parentList);
	}

	@Override
	public List<Object[]> getOperatorList(String userName) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT employee_no, employee_namecn,CONCAT(employee_code,employee_account) ")
				.append(" FROM o2o_employee e,act_org_user u ").append(" WHERE 1=1 ")
				.append(" AND e.employee_no=u.user_no AND u.is_remove = 0 ")
				.append(" AND CONCAT(employee_namecn,employee_account) LIKE ? ")
				.append(" ORDER BY employee_no DESC LIMIT 10 ");
		return super.findObjectsList(sql.toString(), "%" + userName + "%");
	}

	@Override
	public List<Object[]> getOperatorListByRoleCode(String userName, String roleCode) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT DISTINCT employee_no, employee_namecn,CONCAT(employee_code,employee_account) ")
				.append(" FROM o2o_employee e,act_org_role_user u, o2o_act_role r ")
				.append(" WHERE e.employee_no=u.user_no and u.role_id=r.id and u.is_remove = 0")
				.append(" AND r.role_code=? ")
				.append(" AND employee_namecn like ? ORDER BY employee_no DESC LIMIT 10 ");
		return super.findObjectsList(sql.toString(), roleCode, "%" + userName + "%");
	}

	@Override
	public List<ActOrgEntity> getOrgListByRoleAndUser(String userNo) {
		StringBuffer sql = new StringBuffer();
		// sql.append("SELECT t2.* FROM act_org t2 WHERE t2.id IN (").append("
		// SELECT t1.org_id FROM act_org_user t1 WHERE t1.user_no=? UNION ALL")
		// .append(" SELECT t3.org_id FROM act_org_role_user AS t3 WHERE
		// t3.user_no=? AND t3.is_remove=0)");
		sql.append(" SELECT t2.* FROM  act_org t2,( ");
		sql.append(" SELECT t1.org_id  FROM act_org_user t1 WHERE t1.user_no=? and t1.is_remove =0 UNION ");
		sql.append(
				" SELECT t3.org_id  FROM act_org_role_user AS t3 WHERE t3.user_no=?  AND t3.is_remove=0) t4 WHERE  t2.id=t4.org_id");
		return super.findEntitys(sql.toString(), ActOrgEntity.class, userNo, userNo);
	}

	@Override
	public ActOrgEntity getOrgByName(String name) {
		String sql = "select * from act_org where org_name=?";
		return super.findEntity(sql, ActOrgEntity.class, name);
	}

	@Override
	public List<ActOrgEntity> getOrgListByUserNo(String userNo) {
		String sql = "select * from act_org o where o.id in(select DISTINCT * from (SELECT ou.org_id from act_org_user ou where ou.user_no = ? and ou.is_remove = 0 "
				+ "union all SELECT oru.org_id from act_org_role_user oru WHERE oru.user_no = ? and oru.is_remove = 0) t)";
		return super.findEntitys(sql.toString(), ActOrgEntity.class, userNo, userNo);
	}

	@Override
	public List<ActOrgEntity> getLeafNodeByParentId(String orgId) {
		List<ActOrgEntity> resultList = new ArrayList<ActOrgEntity>();
		ActOrgEntity orgEntity = getOrgByIds(orgId);
		if (orgEntity.getParentId().equals("-1")) {
			return null;
		}

		List<String> parentIds = new ArrayList<String>();
		parentIds.add(orgEntity.getOrgId());

		List<ActOrgEntity> tempOrgList = getOrgByParentIds(parentIds);
		// 没有叶子节点，返回自己
		if (tempOrgList == null || tempOrgList.isEmpty()) {
			resultList.add(orgEntity);
			return resultList;
		}

		while (tempOrgList != null && !tempOrgList.isEmpty()) {
			resultList.addAll(tempOrgList);
			parentIds.clear();
			for (ActOrgEntity org : tempOrgList) {
				parentIds.add(org.getOrgId());
			}
			tempOrgList = getOrgByParentIds(parentIds);
		}

		return resultList;
	}

	@Override
	public List<ActOrgEntity> getOrgProvince() {
		String sql = " SELECT * FROM act_org org WHERE org.level=2 AND (org.is_natural_province IS NULL OR org.is_natural_province= 1)";
		return super.findEntitys(sql, ActOrgEntity.class);
	}
	
	public List<ActOrgEntity> getOrgProvinceAndHQ() {
		String sql = " SELECT * FROM act_org org WHERE org.level=2 AND (org.is_natural_province IS NULL OR org.is_natural_province= 1) or org.parent_id = '-1'";
		return super.findEntitys(sql, ActOrgEntity.class);
	}

	@Override
	public List<ActOrgEntity> getOrgChildrenByOrgId(String orgId) {
		String sql = " SELECT * FROM act_org org WHERE parent_id = ?";
		return super.findEntitys(sql, ActOrgEntity.class, orgId);
	}

	@Override
	public ActOrgEntity getProvincialByOrgUnito(String unito) {
		String sql = " SELECT a.* FROM act_org a,(SELECT * FROM act_org ) b, (SELECT * FROM act_org ) c WHERE c.parent_id=b.id AND b.parent_id=a.id AND c.unit_no=? ";
		return super.findEntity(sql, ActOrgEntity.class, unito);
	}

	@Override
	public List<ActOrgEntity> getChildProvinceByNodeId(String nodeId) {
		List<ActOrgEntity> provinceList = new ArrayList<ActOrgEntity>();

		List<String> parentIds = new ArrayList<String>();
		parentIds.add(nodeId);
		List<ActOrgEntity> childOrgList = getOrgByParentIds(parentIds);
		while (!CollectionUtils.isEmpty(childOrgList)) {
			parentIds.clear();
			for (ActOrgEntity org : childOrgList) {
				if (org.getOrgLevel() == ActOrgEntity.ORG_LEVEL_PROVINCE
						&& (org.getIsNaturalProvince() == null || org.getIsNaturalProvince() == 1)) {
					provinceList.add(org);
				} else {
					parentIds.add(org.getOrgId());
				}
			}

			if (CollectionUtils.isEmpty(parentIds)) {
				childOrgList = null;
			} else {
				childOrgList = getOrgByParentIds(parentIds);
			}
		}
		return provinceList;
	}

	@Override
	public ActOrgEntity getParentProviceByNodeId(String nodeId) {
		ActOrgEntity parentOrg = getOrgByIds(nodeId);
		while (parentOrg != null) {
			if (parentOrg.getOrgLevel() == ActOrgEntity.ORG_LEVEL_PROVINCE
					&& (parentOrg.getIsNaturalProvince() == null || parentOrg.getIsNaturalProvince() == 1)) {
				return parentOrg;
			}
			parentOrg = getOrgByIds(parentOrg.getParentId());
		}
		return null;
	}

	@Override
	public List<ActOrgEntity> getAllPlatformList() {
		String sql = "SELECT * from act_org t where t.`level` = 9 or t.`level` = 4";
		return workflowJdbcSession.findEntitys(sql, ActOrgEntity.class);
	}

	@Override
	public List<ActOrgEntity> getOrgByParentIdAndLevel(String parentId, int level) {
		String sql = "SELECT * from act_org t where t.parent_id = ? and t.`level` = ?";
		return workflowJdbcSession.findEntitys(sql, ActOrgEntity.class, parentId, level);
	}

	@Override
	public List<Object[]> getOrgListLikeNameWithLevels(String orgName, List<Integer> levelList) {
		String sql = "SELECT * from (SELECT unit_no,CONCAT_WS('-',unit_code,org_name) as org_name FROM act_org WHERE /**DATA_PERMISSION(unit_no)**/ level in ( ";
		StringBuffer levelBuff = new StringBuffer("");
		for (Integer level : levelList) {
			levelBuff.append(level).append(",");
		}
		sql += levelBuff.substring(0, levelBuff.length() - 1) + ")) t  where t.org_name LIKE ? LIMIT 10 ";
		return super.findEntitys(sql, Object[].class, "%" + orgName + "%");
	}

	@Override
	public String getProvinceLetterCode(String provinceCode) {
		String letterCode = "HO";
		try {
			String sql = "select p.letter_code from pub_provinces p where p.`code` = ?";
			letterCode = workflowJdbcSession.findString(sql, provinceCode);
			return letterCode;
		} catch (Exception e) {
			return null;
		}
	}
}
